package com.saic.parse.impl;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.saic.parse.IImport;

public class ImportModelParseBitauto implements IImport{

	@Override
	public void importExcel(Connection conn) throws Exception {

		int flag = 0;
		Statement stmt = conn.createStatement();

		//新的id  根据指定表的下一个增加量
		ResultSet resultSetNew = stmt.executeQuery("SELECT auto_increment FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='"+DB_NAME+"' AND TABLE_NAME='t_bitauto_vel_model'");
		resultSetNew.next();
		int newId = resultSetNew.getInt(1);				//车系id新的id
		
		if(flag ==0){
			flag = newId;
		}
		
		String url = null;
		InputStream stream = new FileInputStream("d:/model.xls");  
		HSSFWorkbook wb = new HSSFWorkbook(stream); 
		Sheet sheet = wb.getSheetAt(0);  
		//获取数据
		for(Row row:sheet){
			String a_brand_webid = null;		// 汽车之家主品牌webid 
			String a_brand_name = null;			// 汽车之家主品牌名
			String a_subbrand_webid = null;		// 汽车之家子品牌 webid
			String a_subbrand_name = null;		// 汽车之家子品牌名
			String a_desc = null;				// 描述
			String a_series_webid = null;		// 汽车之家车系webid
			String a_series_name = null;		// 汽车之家车系名
			String a_model_webid = null;		// 汽车之家车型webid
			String a_model_name = null;			// 汽车之家车型名
			String a_model_price = null;		// 汽车之家价格
			
			String x_brand_webid = null;		// xcar主品牌webid
			String x_subbrand_webid = null;		// xcar子品牌webid
			String x_series_webid = null;		// xcar车系webid
			String x_series_name = null;		// xcar车系name
			String x_model_webid = null;		// xcar车型webid
			String x_model_name = null;			// xcar车型名
			String x_model_price = null;		// xcar价格
			
			String b_brand_webid = null;		// bitauto主品牌webid
			String b_subbrand_webid = null;		// bitauto子品牌webid
			String b_series_webid = null;		// bitauto车系webid
			String b_series_name = null;		// bitauto车系name
			String b_model_webid = null;		// bitauto车型webid
			String b_model_name = null;			// bitauto车型名
			String b_model_price = null;		// bitauto价格
			for(Cell cell:row){
				
				//主品牌web_id
				if(cell.getColumnIndex() == 0){
					a_brand_webid = cell.toString();
				}
				//主品牌name
				if(cell.getColumnIndex() == 1){
					a_brand_name = cell.toString();
				}
				//子品牌webid
				if(cell.getColumnIndex() == 2){
					a_subbrand_webid = cell.toString();
				}
				//子品牌name
				if(cell.getColumnIndex() == 3){
					a_subbrand_name = cell.toString();
				}
				//汽车之家车系webid
				if(cell.getColumnIndex() == 4){
					a_series_webid = cell.toString();
				}
				//汽车之家车系名
				if(cell.getColumnIndex() == 5){
					a_series_name = cell.toString();
				}
				//汽车之家车型webid
				if(cell.getColumnIndex() == 6){
					a_model_webid = cell.toString();
				}
				//汽车之家车型name
				if(cell.getColumnIndex() == 7){
					a_model_name = cell.toString();
				}
				//汽车之家车型price
				if(cell.getColumnIndex() == 8){
					a_model_price = cell.toString();
				}
				

				//爱卡车系web_id
				if(cell.getColumnIndex() == 23){
					x_series_webid = cell.toString();
				}
				//爱卡车系name
				if(cell.getColumnIndex() == 24){
					x_series_name = cell.toString();
				}
				//爱卡车型webid
				if(cell.getColumnIndex() == 25){
					x_model_webid = cell.toString();
				}
				//爱卡车型name
				if(cell.getColumnIndex() == 26){
					x_model_name = cell.toString();
				}
				//爱卡车型price
				if(cell.getColumnIndex() == 27){
					x_model_price = cell.toString();
				}
				
				
				//易车车系web_id
				if(cell.getColumnIndex() == 13){
					b_series_webid = cell.toString();
				}
				//易车车系name
				if(cell.getColumnIndex() == 14){
					b_series_name = cell.toString();
				}
				//易车车型webid
				if(cell.getColumnIndex() == 15){
					b_model_webid = cell.toString();
				}
				//易车车型name
				if(cell.getColumnIndex() == 16){
					b_model_name = cell.toString();
				}
				//易车车型price
				if(cell.getColumnIndex() == 17){
					b_model_price = cell.toString();
				}
				
			}
			
//			ResultSet resultSet = stmt.executeQuery("select web_id from t_bitauto_brand as brand,t_bitauto_subbrand as subbrand " +
//					"where brand.web_id=subbrand.brand_web_id and brand.name='" +a_brand_name+
//					"' and subbrand.name='"+a_subbrand_name+"'");
//			
//			while(resultSet.next()){
//				//获取bitauto子品牌id
//				b_subbrand_webid = resultSet.getString(1);
//			}
	
				PreparedStatement pstate = conn.prepareStatement("insert into t_bitauto_vel_model (web_id,name,`desc`,guiding_price,series_web_id) values(?,?,?,?,?)");
				pstate.setString(1, b_model_webid);
				pstate.setString(2, b_model_name);
				pstate.setString(3, b_model_name);
				pstate.setString(4, b_model_price);
				pstate.setString(5, b_series_webid);
				pstate.execute();
				pstate.close();	
				
		}
		//删除新老的数据
		stmt.execute("delete from t_bitauto_vel_model where id<"+flag);	

			
	}

}
